<?php
//No direct access
defined('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.model');

class OwnserviceModelTransferHistories extends JModel
{
	/**
	 * Transfer Histories data array
	 *
	 * @var array
	 */
	var $_data = null;

	/**
	 * Method to get Data
	 *
	 * @access public
	 * @return mixed
	 */
	function getData()
	{
		// Lets load the transaction if it doesn't already exist
		if (empty($this->_data))
		{
			$query = $this->_buildQuery();
			$this->_db->setQuery($query);
			
			$this->_data = $this->_db->loadObjectList();
		}

		return $this->_data;
	}

	function _buildQuery()
	{
		//Initialise variables
		$user			= & JFactory::getUser();
		$userId		= $user->get('id', 0);
		$where		= array();

		$where[]		= 't.transfer_id='.(int) $userId;
		$where[]		= 't.state=4';
		$where[]		= 'curdate() < date_add(t.requested_date,interval 30 day)';

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		 $query		= ' SELECT t.session, t.requested_date, sum(t.amount) AS sumamount, sum(t.total) AS sumtotal, c.iscore, c.code '
							.' FROM #__ownservice_transactions AS t '
							.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id=t.rate_id '
							.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
							.$where
							.' GROUP BY DATE(t.requested_date) ' 
							.' ORDER BY t.requested_date ASC '
						   ;

		return $query;
	}

	function getTransferDate()
	{
		//Initialise variables
		$user			= & JFactory::getUser();
		$userId		= $user->get('id', 0);
		
		$date			=JRequest::getVar('date', date('Y-m-d'), 'post');
		
		$where		= array();
		$where[]		= 't.transfer_id='.(int) $userId;
		$where[]		= 't.state=4';
		$where[]		= '(t.requested_date >= '.$this->_db->Quote($date.' 00:00:00').' AND t.requested_date <= '.$this->_db->Quote($date.' 23:59:59').')';

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query		= ' SELECT t.session, t.requested_date, sum(t.amount) AS sumamount, sum(t.total) AS sumtotal, c.iscore, c.code '
							.' FROM #__ownservice_transactions AS t '
							.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id=t.rate_id '
							.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
							.$where
							.' GROUP BY t.session ' 
						   ;

		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();

		return $rows;
	}

	function getDetailTransfer()
	{
		//Initialise variables
		$user			=& JFactory::getUser();
		$userId		= $user->get('id', 0);
		$ownuser	=& Ownservice::getUser();

		//get vars
		$session		= JRequest::getVar('session', 0, 'post', 'int');
		$date			= JRequest::getVar('date', '', 'post');

		$where		= array();

		$where[]		= 't.transfer_id='.(int) $userId;
		$where[]		= 't.state=4';
		$where[]		= 't.session='.$session;
		$where[]		= '(t.requested_date >= '.$this->_db->Quote($date.' 00:00:00').' AND t.requested_date <= '.$this->_db->Quote($date.' 23:59:59').')';

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query	= '	 SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, '
						.' a.account_name, a.account_type, a.phone_no, c.iscore, c.code AS currencycode '
						.' FROM #__ownservice_transactions AS t '
						.' LEFT JOIN #__users AS u ON u.id=t.user_id '
						.' LEFT JOIN #__ownservice_users AS p ON p.user_id=u.id '
						.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
						.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id=t.rate_id '
						.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
						.$where
						.' GROUP BY t.id '
						.' ORDER BY t.id '
						;
		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();

		return $rows;
	}

	function getAllDetailTransfer()
	{
		//Initialise variables
		$user			=& JFactory::getUser();
		$userId		= $user->get('id', 0);
		$ownuser	=& Ownservice::getUser();

		//get vars
		$session		= JRequest::getVar('session', 0, 'post', 'int');
		$date			= JRequest::getVar('date', '', 'post');

		$where		= array();

		$where[]		= 't.transfer_id='.(int) $userId;
		$where[]		= 't.state=4';
		$where[]		= '(t.requested_date >= '.$this->_db->Quote($date.' 00:00:00').' AND t.requested_date <= '.$this->_db->Quote($date.' 23:59:59').')';

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query		= ' SELECT t.session FROM #__ownservice_transactions AS t '
							.$where
							.' GROUP BY t.session '
							.' ORDER BY t.session '
							;
		$this->_db->setQuery($query);
		$sessions = $this->_db->loadObjectList();

		if($sessions) {
			foreach($sessions as $index=>$session) {
					$query	= '	 SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, '
						.' a.account_name, a.account_type, a.phone_no, c.iscore, c.code AS currencycode '
						.' FROM #__ownservice_transactions AS t '
						.' LEFT JOIN #__users AS u ON u.id=t.user_id '
						.' LEFT JOIN #__ownservice_users AS p ON p.user_id=u.id '
						.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
						.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id=t.rate_id '
						.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
						.$where
						.' AND t.session='.(int) $session->session
						.' GROUP BY t.id '
						.' ORDER BY t.id '
						;
					$this->_db->setQuery($query);
					$rows = $this->_db->loadObjectList();
					
					if($rows) {
						$sessions[$index]->transactions = $rows;
					}
			}
		}

		return $sessions; 
	}
}